package oas.db.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import oas.db.AttendanceRepository;
import oas.db.UserRepository;
import oas.domain.Attendance;
import oas.domain.User;
import oas.web.PaginationSupport;

/**
 * 考勤签到DAO
 *
 * @author dzq
 * @version v1.3.0
 */
@Repository
public class JdbcAttendanceRepository implements AttendanceRepository {

    private JdbcTemplate jdbc;
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    /**
     * 注入jdbcTemplate
     *
     * @param jdbc
     */
    @Autowired
    public JdbcAttendanceRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    @Autowired
    private UserRepository userRepository;

    private class AttendanceRowMapper implements RowMapper<Attendance> {

        @Override
        public Attendance mapRow(ResultSet rs, int rowNum) throws SQLException {
            Long id = rs.getLong("userid");
            User user = userRepository.findById(id.intValue());
            int statusCode = rs.getInt("status");
            String status = "";
            if (statusCode == 0) {
                status = "0";
            } else if (statusCode == 1) {
                status = "1";
            } else {
            }
            return new Attendance(rs.getLong("id"), user, rs.getDate("date"), rs.getTimestamp("attendanceTime"), status);
        }

    }

    /**
     * 添加一个签到记录
     *
     * @param attendance
     * @return
     */
    @Override
    public Attendance save(Attendance attendance) {
        jdbc.update(INSERT_ATTENDANCE, attendance.getUser().getId(), attendance.getStatus(), attendance.getDate(), attendance.getAttendanceTime());
        return attendance;
    }

    /**
     * 查找指定页面大小下某一页的数据
     *
     * @param pageNo
     * @param pageSize
     * @param user
     * @return
     */
    @Override
    public PaginationSupport<Attendance> findPage(int pageNo, int pageSize, User user) {
        int totalCount = (int) count(user);
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1) {
            return new PaginationSupport<Attendance>(new ArrayList<Attendance>(), 0);
        }

        List<Attendance> items = jdbc.query(SELECT_ATTENDANCE_PAGE, new AttendanceRowMapper(), user.getId(), pageSize, startIndex);
        PaginationSupport<Attendance> ps = new PaginationSupport<Attendance>(items, totalCount, pageSize, startIndex);
        return ps;
    }

    /**
     * 个人考勤记录的总数
     *
     * @param user
     * @return 个人考勤记录的总数
     */
    @Override
    public long count(User user) {
        return jdbc.queryForLong("select count(id) from attendance where userId = ?", user.getId());
    }

    @Override
    public long countByUserName(User user) {
        return jdbc.queryForLong("select count(a.id) from attendance as a, user as u where a.userid = u.id and username = ?", user.getUsername());
    }

    @Override
    public long countAll() {
        return jdbc.queryForLong("select count(id) from attendance ");
    }

    /**
     * 根据id查找考勤记录
     *
     * @param id
     * @return 考勤记录
     */
    @Override
    public Attendance findById(Long id) {
        try {
            return jdbc.queryForObject(SELECT_ATTENDANCE_BY_ID, new AttendanceRowMapper(), id);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public PaginationSupport<Attendance> findUsersAttendancesPage(int pageNo, int pageSize, String userName, Date startDate, Date endDate) {

        if (userName != null) {
            User user = new User(null, userName, null, null, null, null, null, 0, null, false);
            int totalCount = (int) countByUserName(user);
            int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
            if (totalCount < 1) {
                return new PaginationSupport<Attendance>(new ArrayList<Attendance>(), 0);
            }
            Logger.getLogger("oas").debug(sdf.format(startDate) + ", " + sdf.format(endDate));
            List<Attendance> items = jdbc.query(SELECT_ATTENDANCE_BY_USER_NAME,
                    new AttendanceRowMapper(), user.getUsername(), sdf.format(startDate), sdf.format(endDate), pageSize, startIndex);
            PaginationSupport<Attendance> ps = new PaginationSupport<Attendance>(items, totalCount, pageSize, startIndex);
            return ps;
        } else {
            int totalCount = (int) countAll();
            int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
            if (totalCount < 1) {
                return new PaginationSupport<Attendance>(new ArrayList<Attendance>(), 0);
            }
            Logger.getLogger("oas").debug(sdf.format(startDate) + ", " + sdf.format(endDate));
            List<Attendance> items = jdbc.query(SELECT_ATTENDANCE_BY_DATE,
                    new AttendanceRowMapper(), sdf.format(startDate), sdf.format(endDate), pageSize, startIndex);
            PaginationSupport<Attendance> ps = new PaginationSupport<Attendance>(items, totalCount, pageSize, startIndex);
            return ps;
        }


    }

    /**
     * 查看该用户今天是否已签到
     *
     * @param user  签到用户
     * @param today 今天
     * @return 是否已签到
     */
    @Override
    public boolean findTodayAttendance(User user, Date today) {
        Long result;
        try {
            result = jdbc.queryForObject(SELECT_TODAY_ATTENDANCE, Long.class, user.getId(), sdf.format(today));
        } catch (EmptyResultDataAccessException e) {
            return false;
        }
        return result == 1L;
    }

    private static final String SELECT_TODAY_ATTENDANCE = "select status from attendance where userId = ? and date = ?";

    private static final String INSERT_ATTENDANCE = "insert into attendance (userid, status, date, attendanceTime) values(?,?,?,?)";

    private static final String SELECT_ATTENDANCE_PAGE = "select id, userid, date, attendanceTime, status "
            + "from attendance "
            + "where userid = ? "
            + "order by id limit ? offset ?";

    private static final String SELECT_ATTENDANCE_BY_ID = "select id, userid, date, attendanceTime, status "
            + "from attendance where id = ? ";

    private static final String SELECT_ATTENDANCE_BY_USER_NAME = "select a.id, a.userid, a.date, a.attendanceTime, a.status "
            + "from attendance as a, user as u "
            + "where u.id = a.userid and u.username = ? and a.date >= ? and a.date <= ? "
            + "order by date desc limit ? offset ?";

    private static final String SELECT_ATTENDANCE_BY_DATE = "select a.id, a.userid, a.date, a.attendanceTime, a.status "
            + "from attendance as a "
            + "where a.date >= ? and a.date <= ? "
            + "order by date desc limit ? offset ?";

}
